/*
 * @Author: 抠脚本人
 * @QQ: 742374184
 * @Date: 2022-12-16 11:11:12
 * @LastEditTime: 2022-12-18 12:20:58
 * @Description: 示例
 * 灵感来之不易,积累创造奇迹
 */
import { DataTypes, SQLite } from "autojs-sqlite";
const mSqlite = new SQLite("test", { force: true });
const Student = mSqlite.rawCreateTable(
	"STUDENT",
	`CREATE TABLE IF NOT EXISTS STUDENT(
		'id' INTEGER PRIMARY KEY AUTOINCREMENT,
		'name' TEXT NOT NULL,
		'age' INTEGER,
		'score' INTEGER
	) `
);
const Teacher = mSqlite.createTable("TEACHER", {
	id: { type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true }, //以id为主键，数字类型自动递增
	name: { type: DataTypes.TEXT, notNull: true }, //姓名不能为空
	man: { type: DataTypes.BOOLEAN, defaultValue: 1 }, //布尔值，0为女教师，1为男教师，默认为男教师
	project: { type: DataTypes.TEXT, unique: true }, //将学科作为唯一键，避免重复添加
	age: DataTypes.INTEGER, //年龄为数字类型，可为空
});
Student.insert([
	{ name: "测试1", age: 15, score: 55 },
	{ name: "测试2", age: 16, score: 66 },
	{ name: "测试3", age: 17, score: 77 },
	{ name: "测试4", age: 18, score: 95 },
]);
console.log(
	Student.delete({
		score: { "<": 60 },
	})
);
Teacher.insert([
	{ name: "王老师", age: 45, project: "语文" },
	{ name: "李老师", age: 36, project: "英语" },
]);
console.log(Teacher.insertItem({ name: "张老师", age: 45, project: "数学" }));
//学科重复,返回-1,代表新增数据失败
console.log(Teacher.insertItem({ name: "赵老师", age: 49, project: "数学" }));
//第二个参数为true,代表遇到唯一键时,更新信息,返回更新的主键值
console.log(Teacher.insertItem({ name: "赵老师", age: 49, project: "数学" }, true));
//修改数学教师的age为42
console.log("更新行数:", Teacher.update({ age: 42 }, { project: "数学" }));
console.log(
	Student.query({
		name: { "!=": "测试4" },
		age: {
			">": 15,
			"<": 19,
		},
	})
);
console.log(
	Teacher.findOne({
		name: "王老师",
	})
);
console.log(Teacher.findByPk(2));
console.log(Teacher.query({ project: "数学" }));
let result = mSqlite.rawQuery("SELECT * FROM STUDENT WHERE name = ?", ["测试4"]);
console.log("result:", result);
mSqlite.rawSql("DELETE FROM STUDENT WHERE name = ?", ["测试1"]);
mSqlite.close();
